数据库死锁原理及解决思路

一、什么是锁？

        数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。

        加锁的目的确保并发更新场景下的数据正确性。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。

1.锁的持有周期

        加锁：实际访问到某个待更新的行时，对其加锁（而非一开始就将所有的锁都一次性持有）

        解锁：事务提交/回滚时（而非语句结束时就释放）

        持有周期就是加锁和解锁之间的实际时间。

2.锁粒度：库、表、页、行

        锁的粒度越细，并发级别越高（实现也更复杂）

        传统关系型数据库，都实现了行级别的锁

3.常见的加锁操作

  • –Insert、Delete、Update（毫无疑问）
  • –Select … lock in share mode、select … for update（显式加锁）
  • –Lock table … read/write （显示加表级锁）
  • –Alter table … / Create Index … （DDL操作引入的加锁）
  • –Flush tables with read lock （备份常用）
  • –Primary Key/Unique Key唯一约束检查

4.常规锁模式

        共享（S)锁：多个事务可封锁一个共享页；任何事务都不能修改该页； 通常是该页被读取完毕，S锁立即被释放。

        排它（X)锁：仅允许一个事务封锁此页；其他任何事务必须等到X锁被释放才能对该页进行访问；X锁一直到事务结束才能被释放。

        更新（U)锁：用来预定要对此页施加X锁，它允许其他事务读，但不允许再施加U锁或X锁；当被读取的页将要被更新时，则升级为X锁；U锁一直到事务结束时才能被释放。

        最容易理解的锁模式，读加共享锁，写加排它锁

        锁的属性

  • LOCK_REC_NOT_GAP（锁记录，1024）
  • LOCK_GAP（锁记录前的GAP，512）
  • LOCK_ORDINARY（同时锁记录+记录前的GAP，0。传说中的Next Key锁）
  • LOCK_INSERT_INTENTION（插入意向锁，2048）

        加上LOCK_GAP，一切难以理解的源头（后面重点分析）

        锁组合（属性 + 模式）

        锁的属性可以与锁模式任意组合。例如：LOCK_REC_NOT_GAP（1024） + LOCK_X（3）

二、什么又是死锁？

        死锁发生在当多个事务访问同一数据对象时，其中每个事务拥有的锁都是其他事务所需的，由此造成每个事务都无法继续下去。简单的说，事务A等待事务B释放他的资源，B又等待A释放他的资源，这样就互相等待就形成死锁。

三、产生死锁的原因：

  1. 系统资源不足。
  2. 事务运行推进的顺序不合适。
    3.资源分配不当等。

        如果系统资源充足，该事务的资源请求都能够得到满足，死锁出现的可能性就很低，否则就会因争夺有限的资源而陷入死锁。其次，事务运行推进顺序与速度不同，也可能产生死锁。

四、产生死锁的四个必要条件：

        只要下面四个条件有一个不具备，系统就不会出现死锁。

  1. 互斥条件。存在多个并发事务（2个或者以上），而某数据对象在一段时间内只能由一个事务占有，不能同时被两个或两个以上的事务占有。如果此时还有其它事务请求该数据对象，则请求者只能等待，直至占有该数据对象的事务用毕释放。

  2. 不可抢占条件。该事务所获得的数据对象在未使用完毕之前，其他事务不能强行地从该事务手中获取该数据对象，而只能由该事务自行释放。

  3. 占有且申请条件。某事务都已经占有了一个数据对象，为了完成事务逻辑，还必须更新的数据对象，但是此新的数据对象又被其他事务在占用，但是它在等待新数据对象的时候，仍然占有已占有的数据对象。

  4. 循环等待条件。存在一个事务等待序列{P1，P2，…，Pn}，其中P1等待P2所占有的某一资源，P2等待P3所占有的某一源，……，而Pn等待P1所占有的的某一资源，形成一个事务循环等待环。

五、如何避免死锁？

        死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。

        打破上述四个条件中的一个，常见解决思路有以下几中：

  1. 按同一顺序访问对象。(注：避免出现循环)
  2. 避免事务中的用户交互。(注：减少持有资源的时间，较少锁竞争)
    因为运行没有用户交互的批处理的速度要远远快于用户手动响应查询的速度。
  3. 保持事务简短并处于一个批处理中。(注：同(2)，减少持有资源的时间)
  4. 使用较低的隔离级别。(注：使用较低的隔离级别（例如已提交读）比使用较高的隔离级别（例如可序列化）持有共享锁的时间更短，减少锁竞争)
  5. 使用基于行版本控制的隔离级别
  6. 使用绑定连接。

六、死锁的排查解决办法（以mysql innodB为例）

        死锁出现的报错信息：“Deadlock found when trying to get lock;”

        如何排查死锁成因。

  1. 通过应用业务日志定位到问题代码，找到相应的事务对应的sql；

        因为死锁被检测到后会回滚，这些信息都会以异常反应在应用的业务日志中，通过这些日志我们可以定位到相应的代码，并把事务的sql给梳理出来。

        命令：

1
show engine innodb status\G;

        一般来说，死锁的原因和处理方式有很多种，主要是数据库系统在设计阶段就要考虑，所以再深入的研究和了解只能专业去研究了，在此不细究。

        锁表

        读锁定

1
mysql>LOCK TABLES tbl_name READ;

        验证：

1
show OPEN TABLES where In_use > 0;  #查询是否锁表

        写锁定

1
mysql>LOCK TABLES tbl_name WRITE;

        解锁（有两种）：

        第一种

1
mysql>UNLOCK TABLES;

        第二种

        步骤：

1
2
3
4
5
6
7
mysql -uxxx -pxxx -h服务器ip --port=服务器端口;（如果服务器设置了ip和端口访问的话，一定要带ip和端口）
show OPEN TABLES where In_use > 0;  #查询是否锁表
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;   #查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;  #查看等待锁的事务
mysql> show processlist; #查看正在执行的sql （show full processlist;查看全部sql）
mysql> kill id #杀死sql进程；

        如果进程太多找不到，就重启mysql

1
/ect/init.d/mysql restart

        或

1
2
/ect/init.d/mysql stop  #如果关不掉就直接kill -9 进程id 
/ect/init.d/mysql start

        去看看mysql日志文件是否保存死锁日志：

        常用目录：/var/log/mysqld.log；

七、表级锁的加锁和解锁过程（以mysql innodB为例）

        mysql 的 表锁 lock tables 感觉就像一个 封闭的空间

        mysql发现 lock tables 命令的时候,会将带有锁标记的表(table) 带入封闭空间,直到 出现 unlock tables 命令 或 线程结束, 才关闭封闭空间。

        进入封闭空间时 , 仅仅只有锁标记的表(table) 可以在里面使用,其他表无法使用。

        锁标记 分为 read 和 write 下面是 两种 锁的区别

        如 将 table1 设为read锁, table2 设为write锁, table3 设为read锁

1
lock tables [table1] read,[table2] write,[table3] read;

        执行到这里时,进入封闭空间。

  1. table1 仅允许[所有人]读,[空间外]如需写、更新要等待[空间退出],[空间内]如需写、更新会引发mysql报错。
  2. table2 仅允许[空间内]读写更新,[空间外]如需写、更新要等待[空间退出]。
  3. table3 仅允许[所有人]读,[空间外]如需写、更新要等待[空间退出],[空间内]如需写、更新会引发mysql报错。

        执行到这里时,退出封闭空间,释放所有表锁

1
unlock tables

        当前线程关闭时,自动退出封闭空间,释放所有表锁,无论有没有执行 unlock tables

        加锁和解锁（表级锁）：

        实验中用到的命令：

1
2
mysql> show engines;   #提供什么存储引擎:
mysql> show variables like '%storage_engine%';  #当前默认的存储引擎: